﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data;

namespace BawangMemo.BackendModels
{
    public class TagRepository
    {
        public static int CreateCourseTagTable(CourseWrapper courseWrapper)
        {
            return CreateCourseTagTable(courseWrapper.CourseName);
        }

        public static int CreateCourseTagTable(string courseName)
        {
            using (OleDbConnection connection = new OleDbConnection(Constants.ConnectionString))
            {
                connection.Open();
                try
                {
                    DataAccess.ExecuteNonQuery(connection,
                        "CREATE TABLE " + courseName + "_tags " +
                        "(WordId int not null, Tag varchar(50) not null, primary key (WordId, Tag))");
                }
                catch
                {
                    return -1;
                }
                finally
                {
                    connection.Close();
                }
            }
            return 0;
        }

        public static int AddTag(WordWrapper wordWrapper, string tag)
        {
            return AddTag(wordWrapper.Course.CourseName, wordWrapper.Id, tag);
        }

        public static int AddTag(string courseName, int wordId, string tag)
        {
            using (OleDbConnection connection = new OleDbConnection(Constants.ConnectionString))
            {
                connection.Open();
                try
                {
                    DataAccess.ExecuteNonQuery(connection,
                        "insert into " + courseName +
                        "_tags (WordId, Tag) values ('" + wordId + "','" + Helper.quote(tag) + "')");
                }
                catch
                {
                    //Console.WriteLine("dup: " + wordId + " " + tag);
                    return -1;
                }
                finally
                {
                    connection.Close();
                }
            }
            return 0;
        }

        public static int RemoveTag(WordWrapper wordWrapper, string tag)
        {
            return RemoveTag(wordWrapper.Course.CourseName, wordWrapper.Id, tag);
        }

        public static int RemoveTag(string courseName, int wordId, string tag)
        {
            using (OleDbConnection connection = new OleDbConnection(Constants.ConnectionString))
            {
                connection.Open();
                try
                {
                    DataAccess.ExecuteNonQuery(connection,
                        "delete from " + courseName +
                        "_tags where WordId = " + wordId + " and Tag = '" + Helper.quote(tag) + "'");
                }
                catch
                {
                    return -1;
                }
                finally
                {
                    connection.Close();
                }
            }
            return 0;
        }

        public static List<string> ListAllTags(CourseWrapper courseWrapper)
        {
            return ListAllTags(courseWrapper.CourseName);
        }

        public static List<string> ListAllTags(string courseName)
        {
            List<string> result = new List<string>();
            using (OleDbConnection connection = new OleDbConnection(Constants.ConnectionString))
            {
                connection.Open();
                try
                {
                    OleDbDataReader reader = DataAccess.ExecuteReader(connection,
                        "SELECT DISTINCT tag FROM " + courseName + "_tags");
                    while (reader.Read())
                    {
                        result.Add((string)reader[0]);
                    }
                }
                catch
                {
                    throw;
                }
                finally
                {
                    connection.Close();
                }
            }
            return result;
        }

        public static List<string> ListWordTags(WordWrapper wordWrapper)
        {
            return ListWordTags(wordWrapper.Course.CourseName, wordWrapper.Id);
        }

        public static List<string> ListWordTags(string courseName, int wordId)
        {
            List<string> result = new List<string>();
            using (OleDbConnection connection = new OleDbConnection(Constants.ConnectionString))
            {
                connection.Open();
                try
                {
                    OleDbDataReader reader = DataAccess.ExecuteReader(connection,
                        "SELECT tag FROM " + courseName +
                        "_tags WHERE WordId = " + wordId);
                    while (reader.Read())
                    {
                        result.Add((string)reader[0]);
                    }
                }
                catch
                {
                    throw;
                }
                finally
                {
                    connection.Close();
                }
            }
            return result;
        }

        public static List<WordWrapper> ListWordsOfTag(CourseWrapper courseWrapper, string tag)
        {
            List<int> indexes = ListWordsOfTag(courseWrapper.CourseName, tag);
            return (from index in indexes select courseWrapper.WordList[index - 1]).ToList();
        }

        public static List<int> ListWordsOfTag(string courseName, string tag)
        {
            List<int> result = new List<int>();
            using (OleDbConnection connection = new OleDbConnection(Constants.ConnectionString))
            {
                connection.Open();
                try
                {
                    OleDbDataReader reader = DataAccess.ExecuteReader(connection,
                        "SELECT WordId FROM " + courseName +
                        "_tags WHERE Tag = '" + Helper.quote(tag) + "'");
                    while (reader.Read())
                    {
                        result.Add((int)reader[0]);
                    }
                }
                catch
                {
                    throw;
                }
                finally
                {
                    connection.Close();
                }
            }
            return result;
        }

        internal static void BulkInsert(CourseWrapper cw,
            List<KeyValuePair<WordWrapper, string>> insertList)
        {
            DataTable dt = LoadDataTable(cw, Constants.ConnectionString);
            
            // fill data
            foreach (KeyValuePair<WordWrapper, string> kvp in insertList)
            {
                DataRow dr = dt.NewRow();
                dr["WordId"] = kvp.Key.Id;
                dr["Tag"] = kvp.Value;
                dt.Rows.Add(dr);
            }

            UpdateDataTable(cw, dt, Constants.ConnectionString);
        }

        private static void UpdateDataTable(CourseWrapper cw, DataTable dt, string connString)
        {
            string tableName = cw.CourseName + "_tags";
            string SQL = "SELECT * FROM MeterReadings WHERE ReadingID=0";
            string INSERT = "INSERT INTO " + tableName + "(WordId, Tag) " +
                            "VALUES (@WordId, @Tag)";

            OleDbConnection OleConn = new OleDbConnection(connString);
            OleDbDataAdapter OleAdp = new OleDbDataAdapter(SQL, OleConn);
            OleAdp.InsertCommand = new OleDbCommand(INSERT);
            OleAdp.InsertCommand.Parameters.Add("@WordId", OleDbType.Integer, 4, "WordId");
            OleAdp.InsertCommand.Parameters.Add("@Tag", OleDbType.VarChar, 50, "Tag");
            OleAdp.InsertCommand.Connection = OleConn;
            OleAdp.InsertCommand.Connection.Open();
            OleAdp.Update(dt);
            OleAdp.InsertCommand.Connection.Close();
        }

        private static DataTable LoadDataTable(CourseWrapper cw, string connString)
        {
            DataTable dt = new DataTable();
            string tableName = cw.CourseName + "_tags";
            string SQL = "SELECT * FROM " + tableName + " WHERE WordId = -1";
            OleDbConnection OleConn = new OleDbConnection(connString);
            OleDbDataAdapter OleAdp = new OleDbDataAdapter(SQL, OleConn);
            OleConn.Open();
            OleAdp.Fill(dt);
            OleConn.Close();
            return dt;
        }

        internal static void DeleteCourseTagTable(string courseName)
        {
            using (OleDbConnection connection = new OleDbConnection(Constants.ConnectionString))
            {
                connection.Open();
                try
                {
                    DataAccess.ExecuteNonQuery(connection,  "DROP TABLE " + courseName + "_tags");
                }
                catch
                {
                }
                finally
                {
                    connection.Close();
                }
            }
        }

        public static int CountWordsOfTag(CourseWrapper _courseWrapper, string tag)
        {
            string tableName = _courseWrapper.CourseName + "_tags";
            using (OleDbConnection connection = new OleDbConnection(Constants.ConnectionString))
            {
                connection.Open();
                var reader = DataAccess.ExecuteReader(connection,
                    String.Format(@"SELECT COUNT(*) FROM {0} WHERE Tag='{1}'",
                    tableName, tag));
                if (reader.Read())
                {
                    return reader.GetInt32(0);
                }
                else
                {
                    return 0;
                }
            }
        }
    }
}
